package com.cloudpivot.receivales.excel.repository;

import com.cloudpivot.receivales.excel.entity.SalePlanEntity;
import com.cloudpivot.receivales.excel.entity.SalePlanPersonEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 计算跟进数据的Repository接口2
 */
@Repository
public interface SalePlanCalPersonEntityRepository extends JpaRepository<SalePlanPersonEntity,String> {

    /**
     * 获取人员信息矩阵
     */
    @Query(nativeQuery = true,value =  " SELECT d.id AS id," +
            "    SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) AS `deptName`, " +
            "    d.name AS `userName` " +
            "FROM " +
            "    h_org_user d " +
            "WHERE " +
            "    d.status = 'ENABLE' and d.name not in ('翟中培','吴孟美','叶遇春','曾清发') and SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) <>'已离职人员' and SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) <> '' and (SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) like '浙江博圣生物技术股份有限公司/营销中心/营销一区%' or SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) like '浙江博圣生物技术股份有限公司/营销中心/营销二区%' or SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) like '浙江博圣生物技术股份有限公司/营销中心/营销三区%' or SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) like '浙江博圣生物技术股份有限公司/营销中心/公卫部%' or SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) like '浙江博圣生物技术股份有限公司/营销中心/渠道拓展部%') and d.position not in ('副总监','总监','轮值总监','销售助理','销售内勤')" +
            "ORDER BY " +
            "    `deptName`, " +
            "    d.name")
    List<SalePlanPersonEntity> getAllPerson();
}
